﻿//Copyrigh (C) 2021 上海东欣软件工程有限公司 版权所有。
//<author>邢耀光</author>
//<data>2021.1.31</data>

using Core;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace Dal
{
    /// <summary>
    /// 部门操作类
    /// </summary>
    internal sealed class DepartmentDao: IDepartmentDao
    {
        /// <summary>
        /// 添加部门
        /// </summary>
        /// <param name="department">部门信息</param>
        /// <param name="connString">数据库连接字符串</param>
        /// <returns>影响行数，1成功，0失败</returns>
        public int InsertDepartment(Department department, string connString)
        {
            try {
                string format = "INSERT INTO [tbAclDepartment]([ParentID],[Name]) VALUES ({0},N'{1}')";
                string sql = string.Format(format, department.ParentID, department.Name);
                return Dbutil.ExecuteNonQuery(sql, connString);
            } catch (Exception e){
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 修改部门
        /// </summary>
        /// <param name="department">部门信息</param
        /// <param name="connString">数据库连接字符串</param>
        /// <returns>影响行数，1成功，0失败</returns>
        public int UpdateDepartment(Department department, string connString)
        {
            try
            {
                string format = "UPDATE [tbAclDepartment] SET [Name] = N'{0}' WHERE [ID] = {1}";
                string sql = string.Format(format, department.Name, department.ID);
                return Dbutil.ExecuteNonQuery(sql, connString);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }            
        }

        /// <summary>
        /// 删除部门
        /// </summary>
        /// <param name="id">部门id</param>
        /// <param name="connString">数据库连接字符串</param>
        /// <returns>影响行数，1成功，0失败</returns>
        public int DeleteDepartment(long id, string connString)
        {
            try
            {
                string format = "DELETE FROM [tbAclDepartment] WHERE [ID] ={0} ";
                string sql = string.Format(format, id);
                return Dbutil.ExecuteNonQuery(sql, connString);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }            
        }

        /// <summary>
        /// 模糊查询部门
        /// </summary>
        /// <param name="args">部门查询参数信息</param>
        /// <param name="connString">数据库连接字符串</param>
        /// <returns>查询结果集</returns>
        public List<Department> QueryDepartment(Department args, string connString)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select * from tbAclDepartment where 1=1");
                if (args.ID>0)
                {
                    sb.AppendFormat(" AND ID = {0} ", args.ID);
                }
                if (args.ParentID > 0)
                {
                    sb.AppendFormat(" AND ParentID = {0} ", args.ParentID);
                }
                if (!string.IsNullOrEmpty(args.Name))
                {
                    sb.AppendFormat(" AND Name Like N'%{0}%' ", args.Name);
                }
                string sql = sb.ToString();
                List<Department> departments = new List<Department>();
                using (IDataReader reader = Dbutil.ExecuteReader(sql, connString))
                {
                    while (reader.Read())
                    {
                        Department department = DepartmentReader(reader);
                        departments.Add(department);
                    }
                }
                return departments;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }            
        }

        /// <summary>
        /// 精确查询部门
        /// </summary>
        /// <param name="args">部门查询参数信息</param>
        /// <param name="connString">数据库连接字符串</param>
        /// <returns>查询结果集</returns>
        public List<Department> ExactQueryDepartment(Department args, string connString)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select * from tbAclDepartment where 1=1");
                if (args.ID > 0)
                {
                    sb.AppendFormat(" AND ID = {0} ", args.ID);
                }
                if (args.ParentID > 0)
                {
                    sb.AppendFormat(" AND ParentID = {0} ", args.ParentID);
                }
                if (!string.IsNullOrEmpty(args.Name))
                {
                    sb.AppendFormat(" AND Name = N'{0}' ", args.Name);
                }
                string sql = sb.ToString();
                List<Department> departments = new List<Department>();
                using (IDataReader reader = Dbutil.ExecuteReader(sql, connString))
                {
                    while (reader.Read())
                    {
                        Department department = DepartmentReader(reader);
                        departments.Add(department);
                    }
                }
                return departments;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 执行储存过程获取当前部门所有父级列表
        /// </summary>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="departmentID">当前部门ID</param>
        /// <returns>查询结果集</returns>
        public  DataTable GetAncestorDepartments (string connString, int departmentID)
        {
            try
            {
                SqlConnection conn = new SqlConnection(connString);
                SqlCommand cmd = new SqlCommand("ProAclGetAncestorDepartments", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@DepartmentID", SqlDbType.Int, 32));
                //把具体的值传给输入参数
                cmd.Parameters["@DepartmentID"].Value = departmentID;
                //执行存储过程
                SqlDataAdapter myCommand = new SqlDataAdapter();
                myCommand.SelectCommand = cmd;
                DataSet myDataSet = new DataSet();
                myCommand.Fill(myDataSet, "Results");
                return myDataSet.Tables[0];
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 执行储存过程获取当前部门所有子级级列表
        /// </summary>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="departmentID">当前部门ID</param>
        /// <returns>查询结果集</returns>
        public  DataTable GetDescendantDepartments(string connString, int departmentID)
         {
            try
            {
                SqlConnection conn = new SqlConnection(connString);
                SqlCommand cmd = new SqlCommand("ProAclGetDescendantDepartments", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@DepartmentID", SqlDbType.Int, 32));
                //把具体的值传给输入参数
                cmd.Parameters["@DepartmentID"].Value = departmentID;
                //执行存储过程
                SqlDataAdapter myCommand = new SqlDataAdapter();
                myCommand.SelectCommand = cmd;
                DataSet myDataSet = new DataSet();
                myCommand.Fill(myDataSet, "Results");
                return myDataSet.Tables[0];
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }            
        }

        /// <summary>
        /// 根据部门名称判断是否存在
        /// </summary>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="departmentName">部门名称</param>
        /// <returns>是否存在</returns>
        public bool ExistDepartment(string connString, string departmentName)
        {
            try
            {
                Department args = new Department();
                args.Name = departmentName;
                List<Department> departments = ExactQueryDepartment(args, connString);
                return departments.Count > 0;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }            
        }

        /// <summary>
        /// 判断当前部门下的部门名称是否存在
        /// </summary>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="id">当前部门id</param>
        /// <param name="departmentName">部门名称</param>
        /// <returns>是否存在</returns>
        public bool ExistDepartment(string connString, int id,string departmentName)
        {
            try
            {
                Department args = new Department();
                args.ParentID = id;
                args.Name = departmentName;
                List<Department> departments = ExactQueryDepartment(args, connString);
                return departments.Count > 0;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 根据部门id判断是否存在子级部门
        /// </summary>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="departmentID">部门ID</param>
        /// <returns>是否存在</returns>
        public bool ExistDepartmentChildren(string connString, int departmentID)
        {
            try
            {
                Department args = new Department();
                args.ParentID = departmentID;
                List<Department> departments = QueryDepartment(args, connString);
                return departments.Count > 0;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }            
        }

        /// <summary>
        /// 根据部门id判断当前部门是否是根部门
        /// </summary>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="departmentID">部门ID</param>
        /// <returns>是否存在</returns>
        public bool DepartmentIsRoot(string connString, int departmentID)
        {
            try
            {
                Department args = new Department();
                args.ID = departmentID;
                List<Department> departments = QueryDepartment(args, connString);
                return departments[0].ParentID == 0;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }           
        }

        /// <summary>
        /// 根据当前部门id获得下一级部门
        /// </summary>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="departmentID">部门ID</param>
        /// <returns>是否存在</returns>
        public List<Department> GetDepartmentChildrens(string connString, int departmentID)
        {
            try
            {
                Department args = new Department();
                args.ParentID = departmentID;
                List<Department> departments = QueryDepartment(args, connString);
                return departments;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }            
        }

        /// <summary>
        /// 根据当前部门父id获得上一级部门
        /// </summary>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="departmentParentID">部门父ID</param>
        /// <returns>是否存在</returns>
        public List<Department> GetDepartmentParents(string connString, int departmentParentID)
        {
            try
            {
                Department args = new Department();
                args.ID = departmentParentID;
                List<Department> departments = QueryDepartment(args, connString);
                return departments;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }            
        }

        /// <summary>
        /// 获取部门信息
        /// </summary>
        /// <param name="reader">读取的部门信息结果集</param>
        /// <returns>部门信息</returns>
        private static Department DepartmentReader(IDataReader reader)
        {
            try
            {
                Department department = new Department();
                department.ID = reader.GetInt32(reader.GetOrdinal("ID"));
                department.Name = reader.GetString(reader.GetOrdinal("Name"));
                department.ParentID = reader.GetInt32(reader.GetOrdinal("ParentID"));
                department.Creator = reader.GetString(reader.GetOrdinal("Creator"));
                department.CreateTime = reader.GetDateTime(reader.GetOrdinal("CreateTime"));
                department.Modifier = reader.GetString(reader.GetOrdinal("Modifier"));
                department.ModifyTime = reader.GetDateTime(reader.GetOrdinal("ModifyTime"));
                return department;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }            
        }
    }
}
